Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Performance Verification

Once you design and build your system, you must have some way of verifying performance. Verifying the performance of a data warehousing environment is much easier than with OLTP systems because you do not have to simulate large numbers of users.

The simplest method is to test the system using production queries on a test database. By saving the test database and using the same queries on the same data, you can make direct comparisons when you change tuning parameters or upgrade the system.

A good documented test plan allows you not only to verify system performance but to try new things, such as the Parallel Query option, direct-write sorts, or some other new feature you may want to implement in the future. The following sections look at what should be tested in the RDBMS and the operating system.

What To Test in the RDBMS

You should test the RDBMS keeping functionality, load testing, and performance in mind. Make sure that the system will function as specified. Try to use a test database that is configured and sized similar to the production database.

Once you build an effective load test with repeatable results, start changing some of the parameters described in Part II of this book and earlier in this chapter. By changing only one parameter at a time, you can easily see which parameters improve performance and by how much.

Before changing anything, set your expectations for the changes you are going to make. If the results don’t turn out as expected, try to understand why they didn’t. Be sure to log the results so that you have a reference of what changes were effective and what changes were not.

What To Test in the OS

For the most part, the OS is tested with the RDBMS. If you have to change some specific OS parameter to increase a limitation, you usually don’t have to retest the performance. Any limit change is usually associated with an RDBMS change, and the two can be tested together.

Other OS changes such as feature enhancements should be tested with the RDBMS, but you should more closely watch and analyze these results. You should carefully analyze things such as scheduler changes and cache affinity that may have an adverse affect on the system to verify that performance has not degraded.

As stated earlier in this book, the OS is primarily a vehicle for the RDBMS to use. The primary goal in tuning the server OS is to reduce overhead and optimize the I/O, memory, and networking subsystems.

Benchmarks

Standardized benchmarks are a good way to judge how well a system is performing and to use as a comparison between different hardware platforms. If they are available to you, standardized benchmarks are also a good way to analyze the performance of your particular platform.

Although a data warehouse is not specifically a DSS system, it does involve many decision support queries. Because this is so, you may find that a standardized benchmark (such as the TPC-D benchmark) offers some guidance and information. This information may be useful in helping you determine the type of hardware you should purchase.

Using a standardized benchmark (such as the TPC-D benchmark) is not a good way to tune your system. You should tune each system individually, based on its own characteristics. Examine the Full Disclosure Report (FDR) submitted by the test sponsor to see a breakdown of performance by query type. Look at the performance for the particular query types you use in your operation. Doing so may give you some an indication of how well the testing configuration would work in your environment.

A system tuned for a benchmark such as the TPC-D benchmark may provide useful tuning hints. Because the primary goal of a TPC benchmark is for the sponsor to get the best possible performance, you can see how the sponsors have optimally tuned their systems.

Because an official TPC-D benchmark using Oracle is usually submitted by the hardware manufacturers, OS vendors, and Oracle, you can be assured that the system has been tuned as optimally as possible. If you can, obtain a Full Disclosure Report from the TPC (on the Web at this URL: http://www.tpc.org); look at the way the system was designed and tuned.

Of course, the best benchmark to use on your data warehousing system is one of your own production decision support queries. This benchmark provides you with all the information you need to judge the amount and size of the hardware required. You can also use this test to determine whether tuning or hardware changes have improved performance and by how much.

It is also important to benchmark and test the loading and data-manipulation phases used in generating the data warehouse’s data. Because this process can be quite complex and time consuming, do not neglect the performance of this phase or operation. If you combine data from several sources to generate new data, you may be spending several hours a day adding this data.

You can reduce the time it takes to generate this data by properly tuning memory and I/O for this task. Depending on the requirements of the system, it may be more crucial to tune the load phase than the query phase of the daily operations.

Summary

As the price of computer hardware—especially disk drives—comes down every year, the idea of a data warehouse becomes increasingly more feasible. The amount of hardware that a few short years ago would have cost millions of dollars can now be obtained for much less. The performance of this hardware is also increasing at incredible rates. Systems that were considered minicomputers a few years ago are now being replaced by PC servers with twice the capacity at half the cost.

The ongoing reduction in cost and increase in performance will promote the trend of larger and larger databases with better information retrieval. The goal of the data warehouse is to take information from production databases, legacy data, and outside sources and use this information to better your business. As hardware gets faster and faster at less cost, this trend will continue; new applications and ways of looking at your business data will be developed. It’s very exciting to look at the way the RDBMS industry has grown in the last few years. It will be exciting to see where it goes in the next few years.

This chapter looked at the characteristics of the data warehouse as a business model and from the data access perspective. You can use this information to determine how to configure the system to take advantage of the data access patterns. If you understand how the system operates and what affects the performance of the system, you can use this information to design a system that has well-balanced I/Os and that can take full advantage of the computer’s CPU power.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.